package com.boot.core.task.report122.conn

import com.boot.core.task.report122.dto.TaAccidentReport
import org.springframework.stereotype.Component
import java.sql.*

@Component
class MySqlDBConn {

    /**
     * 将同步数据操作处理到指定MySQL数据库中
     * @param oracleList
     */
    //TODO: 原始jdbc,真可以
    fun mysqlOperateReport(oracleList: List<Any>) {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        try {
            Class.forName("com.mysql.jdbc.Driver")

//            var dbURL: String = "jdbc:mysql://192.168.1.32:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL, "root", "root")

//            String dbURL203 = "jdbc:mysql://192.168.1.203:33306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL203, "root", "root")

            val dbURL_traffic_dev = "jdbc:mysql://92.99.9.91:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
            conn = DriverManager.getConnection(dbURL_traffic_dev, "root", "galaxy@123")

            println("connect success ==> MySQL Database ===> traffic_dev <=== Connection 1")
            val sqlReportUpdate = " UPDATE  ta_accident_report  SET  `accept_personnel` = '%s', `accept_party` = '%s', `accept_time` = '%s', `description` = '%s', `type` = %s,  `address` = '%s', `contact_name` = '%s',  `contact_cell_number` = '%s', `is_accident_personnel` = %s,  `content` = '%s', `plate_number_list` = '%s', `gxsj` = %s, `jjdbh` = %s  WHERE `sldbh` = '%s'"
            val num = 0
            for (i in oracleList.indices) {
                val accident: TaAccidentReport = oracleList[i] as TaAccidentReport
                if (accident.getSldbh()?.let { checkHasSLDBH(it) }!!) {
                    updateData(accident)
                    System.out.println("sldbh" + accident.getSldbh().toString() + "   already in db, update it")
                } else {
                    insertData(accident)
                    System.out.println("sldbh" + accident.getSldbh().toString() + " insert it")
                }
            }
            println("operate success ==> MySQL Database ===> traffic_dev <=== mysqlDBDevConn")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                rs?.close()
                stmt?.close()
                ps?.close()
                conn?.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
    }

    private fun checkHasSLDBH(sldbh: String): Boolean {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        var checkHasSlDBH = false
        try {
            Class.forName("com.mysql.jdbc.Driver")

//            var dbURL: String = "jdbc:mysql://192.168.1.32:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL, "root", "root")

//            String dbURL203 = "jdbc:mysql://192.168.1.203:33306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL203, "root", "root")

            val dbURL_traffic_dev = "jdbc:mysql://92.99.9.91:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
            conn = DriverManager.getConnection(dbURL_traffic_dev, "root", "galaxy@123")

            println("connect success ==> MySQL Database ===> traffic_dev <=== checkHasSLDBH")
            val sqlCheck = "SELECT sldbh from ta_accident_report where sldbh = '$sldbh'"
            ps = conn.prepareStatement(sqlCheck)
            rs = ps.executeQuery()
            while (rs.next()) {
                if (rs.getString("sldbh") != "") {
                    checkHasSlDBH = true
                    break
                }
            }
            println("check success ==> MySQL Database ===> traffic_dev <=== checkHasSLDBH")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                if (rs != null) {
                    rs.close()
                    rs = null
                }
                if (stmt != null) {
                    stmt.close()
                    stmt = null
                }
                if (ps != null) {
                    ps.close()
                    ps = null
                }
                if (conn != null) {
                    conn.close()
                    conn = null
                }
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
        return checkHasSlDBH
    }

    private fun updateData(accident: TaAccidentReport) {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        try {
            Class.forName("com.mysql.jdbc.Driver")

//            var dbURL: String = "jdbc:mysql://192.168.1.32:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL, "root", "root")

//            String dbURL203 = "jdbc:mysql://192.168.1.203:33306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL203, "root", "root")
//
            val dbURL_traffic_dev = "jdbc:mysql://92.99.9.91:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
            conn = DriverManager.getConnection(dbURL_traffic_dev, "root", "galaxy@123")

//            println("connect success ==> MySQL Database ===> traffic_dev <=== updateData")
            var sqlReportUpdate = ""
            sqlReportUpdate = if (accident.getGxsj() == null || accident.getGxsj().equals("null") || accident.equals("")) {
                " UPDATE  ta_accident_report  SET  " +
                        "accept_personnel = '" + accident.getAccept_personnel() + "'," +
                        "accept_party = '" + accident.getAccept_party() + "'," +
                        "accept_time  = '" + accident.getAccept_time() + "'," +
                        "description  = '" + accident.getDescription() + "'," +
                        "type  = '" + accident.getType() + "'," +
                        "address  = '" + accident.getAddress() + "'," +
                        "contact_name  = '" + accident.getContact_name() + "'," +
                        "contact_cell_number  = '" + accident.getContact_cell_number() + "'," +
                        "is_accident_personnel  = '" + accident.getIs_accident_personnel() + "'," +
                        "plate_number_list  = '" + accident.getPlate_number_list() + "'," +
                        "gxsj  = " + accident.getGxsj() + "," +
                        "jjdbh  = '" + accident.getJjdbh() + "' " +
                        " WHERE sldbh='" + accident.getSldbh() + "'"
            } else {
                " UPDATE  ta_accident_report  SET  " +
                        "accept_personnel = '" + accident.getAccept_personnel() + "'," +
                        "accept_party = '" + accident.getAccept_party() + "'," +
                        "accept_time  = '" + accident.getAccept_time() + "'," +
                        "description  = '" + accident.getDescription() + "'," +
                        "type  = '" + accident.getType() + "'," +
                        "address  = '" + accident.getAddress() + "'," +
                        "contact_name  = '" + accident.getContact_name() + "'," +
                        "contact_cell_number  = '" + accident.getContact_cell_number() + "'," +
                        "is_accident_personnel  = '" + accident.getIs_accident_personnel() + "'," +
                        "plate_number_list  = '" + accident.getPlate_number_list() + "'," +
                        "gxsj  = '" + accident.getGxsj() + "'," +
                        "jjdbh  = '" + accident.getJjdbh() + "' " +
                        " WHERE sldbh='" + accident.getSldbh() + "'"
            }
            ps = conn.prepareStatement(sqlReportUpdate)
            ps.execute()
            println("update success ==> MySQL Database ===> traffic_dev <=== updateData")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                if (rs != null) {
                    rs.close()
                    rs = null
                }
                if (stmt != null) {
                    stmt.close()
                    stmt = null
                }
                if (ps != null) {
                    ps.close()
                    ps = null
                }
                if (conn != null) {
                    conn.close()
                    conn = null
                }
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
    }

    private fun insertData(accident: TaAccidentReport) {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        try {
            Class.forName("com.mysql.jdbc.Driver")

//            var dbURL: String = "jdbc:mysql://192.168.1.32:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL, "root", "root")

//            String dbURL203 = "jdbc:mysql://192.168.1.203:33306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
//            conn = DriverManager.getConnection(dbURL203, "root", "root")

            val dbURL_traffic_dev = "jdbc:mysql://92.99.9.91:3306/traffic_dev?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false"
            conn = DriverManager.getConnection(dbURL_traffic_dev, "root", "galaxy@123")

//            println("connect success ==> MySQL Database ===> traffic_dev <=== insertData")
            var sqlReportInsert = ""
            sqlReportInsert = if (accident.getGxsj() == null || accident.getGxsj().equals("null") || accident.equals("")) {
                " INSERT INTO ta_accident_report (accept_personnel,accept_party,accept_time,accident_id,description,type,address,contact_name, contact_cell_number, " +
                        "is_accident_personnel, content, plate_number_list,add_time,jjdbh,cjsj,gxsj,sldbh,xqdw,xzqh) values (" +
                        "'" + accident.getAccept_personnel() + "'," +
                        "'" + accident.getAccept_party() + "'," +
                        "'" + accident.getAccept_time() + "',-1," +
                        "'" + accident.getDescription() + "'," +
                        "'" + accident.getType() + "'," +
                        "'" + accident.getAddress() + "'," +
                        "'" + accident.getContact_name() + "'," +
                        "'" + accident.getContact_cell_number() + "'," +
                        "'" + accident.getIs_accident_personnel() + "'," +
                        "'" + accident.getContent() + "'," +
                        "'" + accident.getPlate_number_list() + "'," +
                        "'" + accident.getAdd_time() + "'," +
                        "'" + accident.getJjdbh() + "'," +
                        "'" + accident.getCjsj() + "'," +
                        " " + accident.getGxsj() + " ," +
                        "'" + accident.getSldbh() + "'," +
                        "'" + accident.getXqdw() + "'," +
                        "'" + accident.getXzqh() + "')"
            } else {
                " INSERT INTO ta_accident_report (accept_personnel,accept_party,accept_time,accident_id,description,type,address,contact_name, contact_cell_number, " +
                        "is_accident_personnel, content, plate_number_list,add_time,jjdbh,cjsj,gxsj,sldbh,xqdw,xzqh) values (" +
                        "'" + accident.getAccept_personnel() + "'," +
                        "'" + accident.getAccept_party() + "'," +
                        "'" + accident.getAccept_time() + "',-1," +
                        "'" + accident.getDescription() + "'," +
                        "'" + accident.getType() + "'," +
                        "'" + accident.getAddress() + "'," +
                        "'" + accident.getContact_name() + "'," +
                        "'" + accident.getContact_cell_number() + "'," +
                        "'" + accident.getIs_accident_personnel() + "'," +
                        "'" + accident.getContent() + "'," +
                        "'" + accident.getPlate_number_list() + "'," +
                        "'" + accident.getAdd_time() + "'," +
                        "'" + accident.getJjdbh() + "'," +
                        "'" + accident.getCjsj() + "'," +
                        "'" + accident.getGxsj() + "'," +
                        "'" + accident.getSldbh() + "'," +
                        "'" + accident.getXqdw() + "'," +
                        "'" + accident.getXzqh() + "')"
            }
            ps = conn.prepareStatement(sqlReportInsert)
            ps.execute()
            println("insert success ==> MySQL Database ===> traffic_dev <=== insertData")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                rs?.close()
                stmt?.close()
                ps?.close()
                conn?.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
    }

}